/*** 本文件由CSMIS2.0-ModelCreater(UTF-8)工具辅助生成，版本1.0 ***/

package com.ce.sys.person.logic.search;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.ce.framework.datamaintain.logic.ISQLMapper;
import com.ce.framework.datamaintain.logic.SQLSearchCondition;
import com.ce.framework.exception.AppException;
import com.ce.framework.util.StringUtil;
import com.ce.sys.login.model.User;
import com.ce.sys.person.view.SysPersonView;
import com.ce.util.OrgUtil;

/**
* 嫦娥软件有限公司 版权所有 2013
* 创建人：　andy
* 创建日期：2010-09-27
* 创建时间：10:08:24
* 功能描述：人员表 [SYS_T_PERSON]条件过滤类
* ==============================================
* 修改历史
* 修改人		修改时间		修改原因
*
* ==============================================
*/
public class SysPersonSearchCondition  extends SQLSearchCondition
	implements ISQLMapper
{
	private String names;  //姓名
	
	private String sex;   //性别
	
	private String bage;  //开始年龄
	
	private String eage;  //结束年龄
	
	private String status;  //用户状态
	
	private String chieftype;  //人员登记
	
	private String orgId;
	
	private String logname;  //账号名
	/** 是否查询子机构下的人员，为0不查询，1查询 */
	private int srhChild;

	public String getSearchSql() throws AppException {
		
		String sql = "select * from (select t.*,ceil(months_between(sysdate, t.dt_born) / 12) as age,p.VC_NAME as VC_ORG$NAME," +
				"(select t1.vc_item$name from sys_t_dict$item t1 where t1.vc_del$flag=0 and t1.vc_item$number =t.vc_worktype and t1.vc_m$id = (select m1.vc_id from sys_t_dict$m m1 where  m1.vc_del$flag=0 and m1.vc_name='人员管理_工种') ) as worktypename," +
				"(select t1.vc_item$name from sys_t_dict$item t1 where t1.vc_del$flag=0 and t1.vc_item$number =t.vc_position and t1.vc_m$id = (select m1.vc_id from sys_t_dict$m m1 where m1.vc_del$flag=0 and m1.vc_name='人员管理_职务或职名') ) as positionname," +
				"(select t1.vc_item$name from sys_t_dict$item t1 where t1.vc_del$flag=0 and t1.vc_item$number =t.VC_CHIEF$TYPE and t1.vc_m$id = (select m1.vc_id from sys_t_dict$m m1 where  m1.vc_del$flag=0 and m1.vc_name='人员管理_职务等级') ) as chieftypename," +
				"(select t1.vc_item$name from sys_t_dict$item t1 where t1.vc_del$flag=0 and t1.vc_item$number =t.vc_grade and t1.vc_m$id = (select m1.vc_id from sys_t_dict$m m1 where  m1.vc_del$flag=0 and m1.vc_name='人员管理_文化程度') ) as gradename," +
				"(select t1.vc_item$name from sys_t_dict$item t1 where t1.vc_del$flag=0 and t1.vc_item$number =t.vc_title and t1.vc_m$id = (select m1.vc_id from sys_t_dict$m m1 where  m1.vc_del$flag=0 and m1.vc_name='人员管理_职称或技术等级') ) as titlename," +
				"(select t1.vc_item$name from sys_t_dict$item t1 where t1.vc_del$flag=0 and t1.vc_item$number =t.VC_POLITICS and t1.vc_m$id = (select m1.vc_id from sys_t_dict$m m1 where  m1.vc_del$flag=0 and m1.vc_name='人员管理_政治面貌') ) as politicsname," +
				"(select t1.vc_filename from pub_t_file_upload_log t1 where t1.vc_id = t.VC_FILEID) as filename " +
				" from sys_t_person t, sys_v_org$dep p where t.vc_org$id = p.VC_ID(+)) person where person.vc_del$flag = 0";
        if(StringUtil.isNotBlank(this.names))
        	sql = sql +(" AND person.VC_NAME LIKE '%" + this.getSafeValue(this.names) + "%' ");
        if(StringUtil.isNotBlank(this.bage))
        	sql = sql +(" AND person.age >=" + this.bage );
        if(StringUtil.isNotBlank(this.eage))
        	sql = sql +(" AND person.age <=" + this.eage );
        if(StringUtil.isNotBlank(this.sex))
        	sql = sql +(" AND person.VC_SEX = " + this.sex );
        if(StringUtil.isNotBlank(this.chieftype))
        	sql = sql +(" and person.VC_CHIEF$TYPE = '"+this.chieftype+"'");
        if(StringUtil.isNotBlank(this.status))
        	sql = sql +(" and person.VC_STATUS = " +this.status);
        if(StringUtil.isBlank(orgId)){
        	User user = User.getUser();
        	if(user!=null)
        		orgId = user.getOrgManProId();
        }
        if(StringUtil.isNotBlank(this.orgId)){
        	if(srhChild==1){
        		if(orgId.equals(OrgUtil.getRootOrgId())){
        			//根机构查询所有人员
        		}
        		else{
        			sql = sql +(" and person.VC_ORG$ID in (select vc_id from sys_v_org$dep start with vc_id = '" +this.orgId+"' connect by vc_par$orgid=prior vc_id)");
        		}
        	}
        	else{
            	sql = sql +(" and person.VC_ORG$ID = '" +this.orgId+"'");
        	}
        }
        if(StringUtil.isNotBlank(this.logname))
        	sql = sql + (" and person.VC_LOG$NAME like '%" +this.getSafeValue(this.logname)+"%'");

		//排序
		if(StringUtil.isNotBlank(this.order)){
            sql = sql + " order by "+ this.getOrderBy()+",person.n_seq,person.VC_NAME asc";
		}else{
			sql = sql + " order by person.n_seq,person.VC_NAME asc";
		}
		return sql;
	}
	
	public Object fromSQLQuery(ResultSet rs, int num) {
		SysPersonView view = new SysPersonView();
		try {

			view.setVcId(rs.getString("VC_ID"));
			view.setVcNumber(rs.getString("VC_NUMBER"));
			view.setVcOrgId(rs.getString("VC_ORG$ID"));
			view.setVcOrgName(rs.getString("VC_ORG$NAME"));
			view.setVcName(rs.getString("VC_NAME"));
			view.setVcStatus(rs.getInt("VC_STATUS"));
			view.setVcPid(rs.getString("VC_PID"));
			view.setVcPwd(rs.getString("VC_PWD"));
			view.setVcSex(rs.getInt("VC_SEX"));
			view.setDtBorn(rs.getDate("DT_BORN"));
			view.setVcTitle(rs.getString("titlename"));
			view.setVcPosition(rs.getString("positionname"));
			view.setVcTel(rs.getString("VC_TEL"));
			view.setVcMobile(rs.getString("VC_MOBILE"));
			view.setVcPhone(rs.getString("VC_PHONE"));
			view.setVcNote(rs.getString("VC_NOTE"));
			view.setNid(rs.getInt("N_ID"));
			view.setVcMailName(rs.getString("VC_MAIL$NAME"));
			view.setVcWorktype(rs.getString("worktypename"));
			view.setVcHome(rs.getString("VC_HOME"));
			view.setVcAddress(rs.getString("VC_ADDRESS"));
			view.setVcPolitics(rs.getString("politicsname"));
			view.setVcJoin(rs.getDate("VC_JOIN"));
			view.setVcGrade(rs.getString("gradename"));
			view.setVcCrtr(rs.getString("VC_CRTR"));
			view.setVcCrtrdate(rs.getDate("VC_CRTRDATE"));
			view.setVcChiefType(rs.getString("chieftypename"));
			view.setVcLastNumber(rs.getString("VC_LAST_NUMBER"));
			view.setVcPhotoId(rs.getString("VC_PHOTO$ID"));
			view.setNseq(rs.getInt("N_SEQ"));
			view.setVcDelFlag(rs.getInt("VC_DEL$FLAG"));
			view.setVcLogName(rs.getString("VC_LOG$NAME"));		
			view.setAge(rs.getInt("age"));
            view.setFilename(rs.getString("filename"));

		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return view;
	}
	
	public ISQLMapper getSQLMapper() {
		
		return this;
	}

	public String getNames() {
		return names;
	}

	public void setNames(String names) {
		this.names = names;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getBage() {
		return bage;
	}

	public void setBage(String bage) {
		this.bage = bage;
	}

	public String getEage() {
		return eage;
	}

	public void setEage(String eage) {
		this.eage = eage;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getChieftype() {
		return chieftype;
	}

	public void setChieftype(String chieftype) {
		this.chieftype = chieftype;
	}

	public String getOrgId() {
		return orgId;
	}

	public void setOrgId(String orgId) {
		this.orgId = orgId;
	}

	public String getLogname() {
		return logname;
	}

	public void setLogname(String logname) {
		this.logname = logname;
	}

	public int getSrhChild() {
		return srhChild;
	}

	public void setSrhChild(int srhChild) {
		this.srhChild = srhChild;
	}

}

